package db.tables;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map.Entry;

import org.hsqldb.Types;

import util.Constant;
import db.DBMain;
import db.DBObject;

public class DBUserTable extends DBObject {

	public static final String TABLE_NAME = "user";

	public static final String USER_ID_COL = "user_id", E_MAIL_COL = "e_mail",
			PASSWORD_COL = "password", USER_ROLE_COL = "user_role",
			HOMEPAGE_COL = "homepage", BIRTHDAY_COL = "birthday",
			SEX_COL = "sex", AVATAR_COL = "avatar";

	public DBUserTable() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ USER_ID_COL, Types.BIGINT, SQL_IDENTITY_PRIMARY_KEY, "" },
				{ E_MAIL_COL, Types.VARCHAR, SQL_NOT_NULL, "" },
				{ PASSWORD_COL, Types.VARCHAR, SQL_NOT_NULL, "SwordFish" },
				{ USER_ROLE_COL, Types.BIGINT, SQL_NOT_NULL, 
					"SELECT "+DBCategoryTable.CATEGORY_ID_COL+" FROM "+DBCategoryTable.TABLE_NAME+" WHERE "+
					DBCategoryTable.CATEGORY_NAME_COL+" = "+Constant.Type.Category.ROLE_USER+";"},
				{ HOMEPAGE_COL, Types.VARCHAR, "", "" },
				{ BIRTHDAY_COL, Types.DATE, "", "" },
				{ AVATAR_COL, Types.VARCHAR, "", "Your picture" },
				{ SEX_COL, Types.BIGINT,getSQLUnique(E_MAIL_COL), ""}});
	}

	public Long getUserID(String e_mail, String password_hash) {
		Long user_id = null;
		ResultSet rs = null;
		PreparedStatement ps=null;
		try {
			ps = prepareStatement("SELECT " + USER_ID_COL
					+ " FROM " + tableName + " WHERE " + E_MAIL_COL
					+ " LIKE ? AND " + PASSWORD_COL + " = ?;");
			ps.setString(1, e_mail.toLowerCase());
			ps.setString(2, password_hash);
			rs = ps.executeQuery();
			//if(rs.isBeforeFirst()){
				if(rs.next())
					user_id = rs.getLong(USER_ID_COL);
			//}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
			closeResult(rs);
		}
		return user_id;
	}
	
	public Long getUserRole(Long user_id){
		return selectLong(USER_ROLE_COL, USER_ID_COL, String.valueOf(user_id));
	}
	
	public void setUserRole(Long role,Long user_id){
		updateCell(USER_ROLE_COL, role, USER_ID_COL, user_id);
	}
	
	protected void insertDefaultValues(){
		String[]cols={E_MAIL_COL,PASSWORD_COL,USER_ROLE_COL,HOMEPAGE_COL,BIRTHDAY_COL,SEX_COL};
		Long male=null, female=null,unknown=null,admin=null,mod=null,user=null;
		for(Entry<Long, String> set:DBMain.getCategoryTable().getCategories(Constant.Type.SEX).entrySet()){
			if(set.getValue().equalsIgnoreCase(Constant.Type.Category.SEX_FEMALE))female=set.getKey();
			else if(set.getValue().equalsIgnoreCase(Constant.Type.Category.SEX_MALE))male=set.getKey();
			else unknown=set.getKey();
		}
		for(Entry<Long, String> set:DBMain.getCategoryTable().getCategories(Constant.Type.USER_ROLE).entrySet()){
			if(set.getValue().equalsIgnoreCase(Constant.Type.Category.ROLE_ADMIN))admin=set.getKey();
			else if(set.getValue().equalsIgnoreCase(Constant.Type.Category.ROLE_MOD))mod=set.getKey();
			else user=set.getKey();
		}
		insertColumns(cols, new Object[][]{{"dbae_admin@uni-hi.de","n00p",admin,"http://dbaews08.googlecode.com/",Date.valueOf("2008-05-16"),male},
				{"dbae_mod@uni-hi.de","n00p",mod,"http://dbaews08.googlecode.com/",Date.valueOf("2008-05-16"),female},
				{"dbae_user@uni-hi.de","n00p",user,"http://dbaews08.googlecode.com/",Date.valueOf("2008-05-16"),unknown}});
	}
}
